SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TCMOVI_BANC_Q19]  
/*-------------------------------------------------------*/  
/*---Empresa               OFISIS S.A.                --*/  
/*---Cliente               SANTILLANA                 --*/  
/*---Sistema               Tesoreria                  --*/  
/*---M©dulo                Empresa                    --*/  
/*---Programa              SALDO INICIAL FLUJO DE CAJA--*/  
/*---Script                SP_TCMOVI_BANC_Q19.sql     --*/  
/*---Nombre SP             SP_TCMOVI_BANC_Q19         --*/  
/*---Desarrollado por      Reynaldo Menacho Luyo      --*/  
/*---Fecha Creaci©n        25/09/2001                 --*/  
/*---Base Datos            SQL SERVER        --*/  
/*---Versi©n               2000                       --*/  
/*---Invoca a SP                                      --*/  
/*-------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/*---Modificado 1 por           --*/  
/*---Fecha Modificaci©n         --*/   
/*---Detalle Modificaci©n       --*/  
/*-------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/*---Drop Proc SP_TCMOVI_BANC_Q19                      --*/  
/*---Grant all on SP_TCMOVI_BANC_Q19 to public         --*/   
/*---SP_TCMOVI_BANC_Q19 '01', '2001/08/31', 'SOL'      --*/  
/*-------------------------------------------------------*/  
  
@ISCO_EMPR      TD_VC_002,  
@ISCO_MONE_EXTR TD_VC_003,  
@IDFE_OPER_INIC TD_DT_001,  
@ISCO_MONE      TD_VC_003,  
@ISTI_REPO      TD_VC_001,  
@INAA_INIC      TD_IN_001,  
@INMM_INIC      TD_IN_001  
 As  
CREATE TABLE #TWSALD_FLUJ_1  
 ( SA_INIC      NUMERIC(16,4) NULL)  
  
Declare  
  
  @VSDE_SELE_0001  VARCHAR(300),  
  @VSDE_SELE_0002  VARCHAR(300),  
  @VSDE_SELE_0003  VARCHAR(300),  
  @VSFE_OPER_INIC  VARCHAR(10),  
  @VSFE_OPER_FINA  VARCHAR(10),  
  @VSCA_EMPR       TD_VC_100,  
  @VNFE_0001       TD_IN_001,   
  @VNFE_0002       TD_IN_001,  
  @CSCO_EMPR       TD_VC_003,   
  @CSDE_NOMB       TD_VC_100,   
  @CSTI_TRAN       TD_VC_001,  
  @CSNU_COMP_BANC  TD_VC_008,   
  @CDFE_OPER       TD_DT_001,   
  @CSDE_OBSE       TD_VC_100,   
  @CSCO_MONE       TD_VC_003,   
  @CNFA_TIPO_CAMB  TD_NU_009_004,  
  @CNIM_DETA_NEGA  TD_NU_016_004,   
  @CSTI_SITU       TD_VC_003,   
  @CSNU_CANJ       TD_VC_008,   
  @CSNU_RENO       TD_VC_008,  
  @CSCO_TIPO_DOCU  TD_VC_003,   
  @CSNU_DOCU_CLIE  TD_VC_020,  
  @VNIM_MOVI       TD_NU_016_004,   
  @VSCO_ARUB       TD_VC_003,   
  @VSDE_ARUB       TD_VC_100,   
  @VSCO_RUBR       TD_VC_003 ,  
  @VSDE_RUBR       TD_VC_100,   
  @VSCO_SUBR       TD_VC_003,   
  @VSDE_SUBR       TD_VC_100 ,  
  @CNPO_DIST       TD_NU_009_004,   
  @CNNU_ORDE_0001  TD_IN_001,   
  @CNNU_ORDE_0002  TD_IN_001,  
  @CNNU_ORDE_0003  TD_IN_001,  
  @CSCO_CLIE       TD_VC_020,  
  @VNIM_CONV       TD_NU_016_004,  
  @CSNU_LETR_CLIE  TD_VC_020,  
  @VSNU_ANNO       TD_VC_004,  
  @VNNU_ANNO_ANTE  TD_IN_001,  
  @VSAA_INIC       TD_VC_004,  
  @VSMM_INIC       TD_VC_002,  
  @ISDE_ANNO       VARCHAR(300)  
  
If @ISCO_MONE = @ISCO_MONE_EXTR  
   Begin  
     Select @VSDE_SELE_0001 = '(T1.IM_MOVI * PATINDEX(T1.TI_MOVI_BANC, "I") + T1.IM_MOVI * -PATINDEX(T1.TI_MOVI_BANC, "E"))  
 / POWER(T1.FA_TIPO_CAMB, PATINDEX(RTRIM(LTRIM(STR(PATINDEX(T5.CO_MONE,"DOL")))),"0")) '   
     Select @VSDE_SELE_0002 = ' (T1.IM_MOVI * PATINDEX(T1.TI_MOVI, "I") + T1.IM_MOVI * -PATINDEX(T1.TI_MOVI, "E"))  
 / POWER(T1.FA_TIPO_CAMB, PATINDEX(RTRIM(LTRIM(STR(PATINDEX(T5.CO_MONE,"DOL")))),"0"))'  
     Select @VSDE_SELE_0003 = ' ((T7.IM_DETA_NEGA*T8.PO_DIST)/100 ) / POWER(T1.FA_TIPO_CAMB, 
PATINDEX(RTRIM(LTRIM(STR(PATINDEX(T5.CO_MONE,"DOL")))),"0"))'  
   End  
Else   
 Begin  
   Select @VSDE_SELE_0001 = ' (T1.IM_MOVI * PATINDEX(T1.TI_MOVI_BANC, "I") + T1.IM_MOVI * -PATINDEX(T1.TI_MOVI_BANC, "E"))  
 * POWER(T1.FA_TIPO_CAMB, PATINDEX(T5.CO_MONE,"DOL")) '  
   Select @VSDE_SELE_0002 = ' (T1.IM_MOVI * PATINDEX(T1.TI_MOVI, "I") + T1.IM_MOVI * -PATINDEX(T1.TI_MOVI, "E"))  
 * POWER(T1.FA_TIPO_CAMB, PATINDEX(T5.CO_MONE,"DOL")) '  
   Select @VSDE_SELE_0003 = ' ( (T7.IM_DETA_NEGA*T8.PO_DIST)/100 )  
 * POWER(T1.FA_TIPO_CAMB, PATINDEX(T5.CO_MONE,"DOL")) '  
 End    
  
    Select @VSCA_EMPR = ''  
  
    If @ISCO_EMPR is Not Null  
       Select @VSCA_EMPR = ' T1.CO_EMPR =  "'+@ISCO_EMPR+'" And  '  
     
    SELECT @VNNU_ANNO_ANTE = YEAR (@IDFE_OPER_INIC)   
  
    SELECT @VSNU_ANNO = CONVERT(VARCHAR(4), @VNNU_ANNO_ANTE  )  
  
    SELECT @VSAA_INIC = CONVERT(VARCHAR(4), @INAA_INIC  )  
  
    SELECT @VSMM_INIC = CONVERT(VARCHAR(2), @INMM_INIC  )  
  
 IF @ISTI_REPO = 'R'  
    SELECT @ISDE_ANNO = 'YEAR(T1.FE_OPER) < "'+@VSNU_ANNO+'" '  
 ELSE  
   SELECT @ISDE_ANNO =  ' ( YEAR(T1.FE_OPER) < "'+@VSAA_INIC+'"  
                  Or  (  MONTH(T1.FE_OPER) < "'+@VSMM_INIC+'"  
                         And YEAR(T1.FE_OPER) <= "'+@VSAA_INIC+'" )) '  
  
EXECUTE ( 'INSERT INTO #TWSALD_FLUJ_1 (SA_INIC)   
Select  SUM( ' + @VSDE_SELE_0001 + ' )  
From  TCMOVI_BANC T1, TMCNTA_BANC T5  
Where  '+@VSCA_EMPR+@ISDE_ANNO+'  
And T1.TI_SITU = "ACT"  
And  LEN(T1.CO_SUBR) > 0  
And  T1.CO_EMPR = T5.CO_EMPR  
And  T1.CO_BANC = T5.CO_BANC  
And  T1.NU_CNTA_BANC = T5.NU_CNTA_BANC ')  
  
EXECUTE ( 'INSERT INTO #TWSALD_FLUJ_1 (SA_INIC)   
SELECT  SUM( ' + @VSDE_SELE_0003 + ' )  
FROM TCMOVI_BANC T1, TMCNTA_BANC T5, TMEMPR T6,  
        TDMOVI_BANC T7, TDDISC_CCOS T8  
WHERE  '+@VSCA_EMPR+@ISDE_ANNO+'  
AND     T1.TI_MOVI_BANC = "I"  
AND T1.TI_SITU != "ANU"  
AND  T1.CO_EMPR = T5.CO_EMPR  
AND  T1.CO_BANC = T5.CO_BANC  
AND  T1.NU_CNTA_BANC = T5.NU_CNTA_BANC  
AND  T1.CO_EMPR = T6.CO_EMPR  
AND        T1.CO_EMPR = T7.CO_EMPR  
AND        T1.CO_UNID_CONC = T7.CO_UNID_CONC
AND        T1.CO_BANC = T7.CO_BANC  
AND        T1.NU_CNTA_BANC = T7.NU_CNTA_BANC  
AND        T1.AA_BNCO = T7.AA_BNCO  
AND        T1.MM_BNCO = T7.MM_BNCO  
AND        T1.TI_MOVI_BANC = T7.TI_MOVI_BANC  
AND        T1.NU_COMP_BANC = T7.NU_COMP_BANC  
AND        T7.CO_EMPR = T8.CO_EMPR  
AND        T7.TI_DOCU_ENTI = T8.CO_TIPO_DOCU  
AND        T7.NU_DOCU_ENTI = T8.NU_DOCU_CLIE  
And     LEN(T8.CO_SUBR) > 0')  
  
EXECUTE ( 'INSERT INTO #TWSALD_FLUJ_1 (SA_INIC)   
Select  SUM( ' + @VSDE_SELE_0002 + ' )  
From  TCMOVI_CAJA T1, TMCAJA T5  
Where  '+@VSCA_EMPR+@ISDE_ANNO+'  
And T1.TI_SITU = "ACT"  
And  LEN(T1.CO_SUBR) > 0  
And  T1.CO_EMPR = T5.CO_EMPR  
And  T1.CO_CAJA = T5.CO_CAJA')  
  
EXECUTE ( 'INSERT INTO #TWSALD_FLUJ_1 (SA_INIC)  
Select  SUM( ' + @VSDE_SELE_0003 + ' )  
FROM TCMOVI_CAJA T1, TMCAJA T5, TMEMPR T6, TDMOVI_CAJA T7, TDDISC_CCOS T8  
WHERE  '+@VSCA_EMPR+@ISDE_ANNO+'  
AND     (T1.CO_CAJA != "00000018" AND T1.CO_CAJA != "00000019")   
AND     T1.TI_MOVI = "I"  
AND T1.TI_SITU != "ANU"  
AND  T1.CO_EMPR = T5.CO_EMPR  
AND  T1.CO_CAJA = T5.CO_CAJA  
AND  T1.CO_EMPR = T6.CO_EMPR  
AND        T1.CO_EMPR = T7.CO_EMPR  
AND        T1.CO_CAJA = T7.CO_CAJA  
AND        T1.AA_CAJA = T7.AA_CAJA  
AND        T1.MM_CAJA = T7.MM_CAJA  
AND        T1.TI_MOVI = T7.TI_MOVI  
AND        T1.NU_COMP_CAJA = T7.NU_COMP_CAJA  
AND        T7.CO_EMPR = T8.CO_EMPR  
AND        T7.TI_DOCU_ENTI = T8.CO_TIPO_DOCU  
AND        T7.NU_DOCU_ENTI = T8.NU_DOCU_CLIE  
And     LEN(T8.CO_SUBR) > 0')  
  
  
/* Caso de las Letras */  
  
EXECUTE('DECLARE CU_TCMOVI_BANC_Q19_1 Cursor   
For  
Select  T1.CO_EMPR , T5.CO_MONE , T1.FA_TIPO_CAMB ,  
 T7.IM_DETA_NEGA , T8.NU_CANJ, T8.NU_RENO, T7.CO_ENTI  
FROM TCMOVI_BANC T1, TMCNTA_BANC T5, TMEMPR T6,  TDMOVI_BANC T7,  
        TMLETR_CLIE T8          
WHERE  '+@VSCA_EMPR+@ISDE_ANNO+'  
AND     T1.TI_MOVI_BANC = "I"  
AND  T1.CO_EMPR = T5.CO_EMPR  
AND  T1.CO_BANC = T5.CO_BANC  
AND  T1.NU_CNTA_BANC = T5.NU_CNTA_BANC  
AND  T1.CO_EMPR = T6.CO_EMPR  
AND     T1.CO_EMPR = T7.CO_EMPR  
AND     T1.CO_UNID_CONC = T7.CO_UNID_CONC  
AND     T1.CO_BANC = T7.CO_BANC
AND     T1.NU_CNTA_BANC = T7.NU_CNTA_BANC  
AND     T1.AA_BNCO = T7.AA_BNCO  
AND     T1.MM_BNCO = T7.MM_BNCO   
AND     T1.TI_MOVI_BANC = T7.TI_MOVI_BANC  
AND     T1.NU_COMP_BANC = T7.NU_COMP_BANC  
AND     T7.CO_EMPR = T8.CO_EMPR  
AND     T7.TI_DOCU_ENTI = "LET"  
AND     T7.NU_DOCU_ENTI = T8.NU_LETR_CLIE  
Union All  
Select  T1.CO_EMPR , T5.CO_MONE , T1.FA_TIPO_CAMB ,  
 T7.IM_DETA_NEGA ,  T8.NU_CANJ, T8.NU_RENO, T7.CO_ENTI  
FROM TCMOVI_CAJA T1, TMCAJA T5, TMEMPR T6,  TDMOVI_CAJA T7,  
        TMLETR_CLIE T8          
WHERE  '+@VSCA_EMPR+@ISDE_ANNO+'  
AND      T1.TI_MOVI = "I"  
AND  T1.CO_EMPR = T5.CO_EMPR  
AND  T1.CO_CAJA = T5.CO_CAJA  
AND  T1.CO_EMPR = T6.CO_EMPR  
AND     T1.CO_EMPR = T7.CO_EMPR  
AND     T1.CO_CAJA = T7.CO_CAJA  
AND     T1.AA_CAJA = T7.AA_CAJA  
AND     T1.MM_CAJA = T7.MM_CAJA  
AND     T1.TI_MOVI = T7.TI_MOVI  
AND     T1.NU_COMP_CAJA = T7.NU_COMP_CAJA  
AND     T7.CO_EMPR = T8.CO_EMPR  
AND     T7.TI_DOCU_ENTI = "LET"  
AND     T7.NU_DOCU_ENTI = T8.NU_LETR_CLIE')  
  
 Open   CU_TCMOVI_BANC_Q19_1  
 Fetch  CU_TCMOVI_BANC_Q19_1 INTO  @CSCO_EMPR , @CSCO_MONE , @CNFA_TIPO_CAMB ,  
 @CNIM_DETA_NEGA , @CSNU_CANJ, @CSNU_RENO, @CSCO_CLIE  
  
 Select @VNFE_0001  = @@FETCH_STATUS  
  
 While (@VNFE_0001 = 0)  
   Begin  
     If (@VNFE_0001 < 0)  
         Begin       
     Close CU_TCMOVI_BANC_Q19_1  
    Deallocate CU_TCMOVI_BANC_Q19_1  
    Return  
         End    
  
        If @CSNU_RENO is not null  
           Begin   
             Select @CSNU_LETR_CLIE = NU_DOCU_CLIE  
             From   TDRENO_CLIE  
             Where  CO_EMPR = @CSCO_EMPR  
             And    CO_CLIE = @CSCO_CLIE  
             And    NU_RENO = @CSNU_RENO                
      And    NU_DOCU_CLIE is not null  
   
             Select @CSNU_CANJ = NU_CANJ  
             From   TMLETR_CLIE  
             Where  CO_EMPR = @CSCO_EMPR  
             And    NU_LETR_CLIE = @CSNU_LETR_CLIE  
           End               
  
        If @CSNU_CANJ is not null  
           Begin   
      DECLARE CU_TCMOVI_BANC_Q19_2 Cursor   
      For  
             Select CO_TIPO_DOCU, NU_DOCU_CLIE  
             From   TDCANJ_CLIE  
             Where  CO_EMPR = @CSCO_EMPR  
             And    CO_CLIE = @CSCO_CLIE  
             And    NU_CANJ = @CSNU_CANJ          
             And    CO_TIPO_DOCU is not null  
   
             Open   CU_TCMOVI_BANC_Q19_2  
             Fetch  CU_TCMOVI_BANC_Q19_2 INTO  @CSCO_TIPO_DOCU, @CSNU_DOCU_CLIE  
  
             Select @VNFE_0002  = @@FETCH_STATUS  
  
             While (@VNFE_0002 = 0)  
               Begin  
                 If (@VNFE_0002 < 0)  
                    Begin       
                Close CU_TCMOVI_BANC_Q19_2  
               Deallocate CU_TCMOVI_BANC_Q19_2  
               Return  
                    End    
  
  INSERT INTO #TWSALD_FLUJ_1 (SA_INIC)  
                SELECT CASE WHEN @ISCO_MONE = @ISCO_MONE_EXTR   
                       THEN ((@CNIM_DETA_NEGA*T1.PO_DIST)/100 ) / POWER(@CNFA_TIPO_CAMB, 
PATINDEX(RTRIM(LTRIM(STR(PATINDEX(@CSCO_MONE,'DOL')))),'0'))  
                       ELSE ((@CNIM_DETA_NEGA*T1.PO_DIST)/100 ) * POWER(@CNFA_TIPO_CAMB, PATINDEX(@CSCO_MONE,'DOL')) END    
      
                FROM TDDISC_CCOS T1, TTSUBR_FINA T2, TTRUBR_FINA T3, TTARUB_FINA T4  
                Where T1.CO_EMPR = @CSCO_EMPR  
                AND   T1.CO_TIPO_DOCU = @CSCO_TIPO_DOCU  
                AND   T1.NU_DOCU_CLIE = @CSNU_DOCU_CLIE  
         AND   T2.CO_EMPR = T1.CO_EMPR  
         AND   T2.CO_SUBR = T1.CO_SUBR_LETR  
         AND   T2.ST_REPO = "S"  
         AND   T2.CO_EMPR = T3.CO_EMPR  
         AND   T2.CO_RUBR = T3.CO_RUBR  
         AND   T3.CO_EMPR = T4.CO_EMPR  
         AND   T3.CO_ARUB = T4.CO_ARUB  
         AND   EXISTS ( SELECT T7.CO_RUBR  
  FROM  TTRUBR_FINA T7  
  WHERE  T7.CO_EMPR = T2.CO_EMPR  
  AND  T7.CO_RUBR = T2.CO_RUBR  
  AND  T7.ST_REPO = "S"    AND EXISTS ( SELECT T8.CO_ARUB  
          FROM    TTARUB_FINA T8  
          WHERE T8.CO_EMPR = T7.CO_EMPR  
          AND       T8.CO_ARUB = T7.CO_ARUB  
          AND       T8.ST_REPO = "S"))  
  
               Fetch  CU_TCMOVI_BANC_Q19_2 INTO  @CSCO_TIPO_DOCU, @CSNU_DOCU_CLIE  
  
              Select @VNFE_0002 = @@FETCH_STATUS  
         End  
  
  
        Close CU_TCMOVI_BANC_Q19_2  
        Deallocate CU_TCMOVI_BANC_Q19_2  
      End  
            
     Fetch  CU_TCMOVI_BANC_Q19_1 INTO  @CSCO_EMPR , @CSCO_MONE , @CNFA_TIPO_CAMB ,  
 @CNIM_DETA_NEGA , @CSNU_CANJ, @CSNU_RENO, @CSCO_CLIE  
  
     Select @VNFE_0001 = @@FETCH_STATUS  
   End  
  
 Close CU_TCMOVI_BANC_Q19_1  
 Deallocate CU_TCMOVI_BANC_Q19_1  
  
SELECT Isnull(SUM(ISNULL(SA_INIC,0)),0)  
FROM #TWSALD_FLUJ_1


GO
